/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DataAccess;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.TableModel;

/**
 *
 * @author MrToan
 */
public class AdminFunction {

    

    public int AddUser(String UID,String RID,String Name,String UserName,String Pass,String Phone,String Email,String Address,int Status)
    {
        int InsertSuccess=0;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call Add_User(?,?,?,?,?,?,?,?,?)}");
            smt.setString(1, UID);
            smt.setString(2, RID);
            smt.setString(3, Name);
            smt.setString(4, UserName);
            smt.setString(5,Pass);
            smt.setString(6,Phone);
            smt.setString(7,Email);
            smt.setString(8,Address);
            smt.setInt(9, Status);
            InsertSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return InsertSuccess;
    }

    public TableModel getUserTableValue()
    {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Users where U_Status=1";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            Logger.getLogger(AdminFunction.class.getName()).log(Level.SEVERE, null, ex);
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public int UpdateUser(String UID,String RID,int Status)
    {
        int UpdateSuccess=0;

        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call UpdateUser(?,?,?)}");
            smt.setString(1, UID);
            smt.setString(2, RID);
            smt.setInt(3, Status);
            UpdateSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return UpdateSuccess;
    }

    public String getRID(String RName)
    {
        String RID="";
        ResultSet RS;

        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call getRID(?)}");
            smt.setString(1, RName);
            RS = smt.executeQuery();
            while(RS.next())
            {
                RID=RS.getString("R_ID");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return RID;
    }

    public int getPer(String RID) {
        int per=0;
        ResultSet RS;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call getPermission(?)}");
            smt.setString(1, RID);
            RS = smt.executeQuery();
            while(RS.next())
            {
                per=RS.getInt("R_Permission");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return  per;
    }

    public Vector getCate()
    {
        Vector CateList = new Vector();

        ResultSet RS=null;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call GetCategory}");
            RS = smt.executeQuery();
            while(RS.next())
            {
                CateList.add(RS.getString("C_Name"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return CateList;
    }

    public int getCateID(String CName)
    {
        int CID=0;
        ResultSet RS;

        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call getCateID(?)}");
            smt.setString(1, CName);
            RS = smt.executeQuery();
            while(RS.next())
            {
                CID=RS.getInt("C_ID");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return CID;
    }

    public TableModel getItemTableValue()
    {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Items where I_Status=1";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            Logger.getLogger(AdminFunction.class.getName()).log(Level.SEVERE, null, ex);
        }
        rtm.setQuery(Query);
        return rtm;
    }

    

    public int Add_Items(String IID, int ICate, String IName, String IUnit, int IQuantity, float IPrice, int IStatus) {
         int InsertSuccess=0;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call Add_Items(?,?,?,?,?,?,?)}");
            smt.setString(1, IID);
            smt.setInt(2, ICate);
            smt.setString(3, IName);
            smt.setString(4, IUnit);
            smt.setInt(5,IQuantity);
            smt.setFloat(6,IPrice);
            smt.setInt(7,IStatus);
            InsertSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return InsertSuccess;
    }

    public int UpdateItem(String IID, int ICate, String IName, String IUnit, int IQuantity, float IPrice)
    {
        int UpdateSuccess=0;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call updateItem(?,?,?,?,?,?)}");
            smt.setString(1, IID);
            smt.setInt(2, ICate);
            smt.setString(3, IName);
            smt.setString(4, IUnit);
            smt.setInt(5,IQuantity );
            smt.setFloat(6, IPrice);
            UpdateSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return UpdateSuccess;
    }

    public int DeleteItem(String IID)
    {
        int deleteSuccess=0;

        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call DeleteItem(?)}");
            smt.setString(1, IID);
            deleteSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return deleteSuccess;
    }

    public int GenerateCateID()
    {
        int CateID=0;
        ResultSet RS;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call getCategoryID }");
            RS = smt.executeQuery();
            while(RS.next())
            {
                CateID = RS.getInt("CountID");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return CateID;
    }

    public int AddCategory(String CName,int CStatus)
    {
        int InsertSuccess=0;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call AddCate(?,?)}");
            smt.setString(1, CName);
            smt.setInt(2, CStatus);
            InsertSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return InsertSuccess;
    }

    public TableModel getCateTableValue()
    {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Categories where C_Status=1";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            Logger.getLogger(AdminFunction.class.getName()).log(Level.SEVERE, null, ex);
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public int UpdateCate(int CID,String CName)
    {
        int UpdateSuccess=0;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call updateCate(?,?)}");
            smt.setInt(1, CID);
            smt.setString(2, CName);
            UpdateSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return UpdateSuccess;
    }

    public int DeleteCate(int CID)
    {
        int deleteSuccess=0;

        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call DeleteCate(?)}");
            smt.setInt(1, CID);
            deleteSuccess = smt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return deleteSuccess;
    }

    public int updateDateInfor_DA(String UID, String Name, String Phone, String Email, String Address) {
        System.out.println("IDDDDDDDDDDDDDD "+ UID);
        int update=0;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call updateInfor(?,?,?,?,?)}");
            smt.setString(1, UID);
            smt.setString(2, Name);
            smt.setString(3, Phone);
            smt.setString(4, Email);
            smt.setString(5, Address);
            update = smt.executeUpdate();
            System.out.println(""+smt.executeUpdate());
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return update;
    }

    public int changePass_DA(String userID, String NewPass) {
        int change=0;
        try {
            CallableStatement changePass = ConnectionDB.getConnection().prepareCall("{call sp_changePass(?,?)}");
            changePass.setString(1, userID);
            changePass.setString(2, NewPass);
            change=changePass.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return change;
    }
}

